package com.epam.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.epam.dao.exception.TourNotAvailableException;
import com.epam.dao.intf.IPurchaseDAO;
import com.epam.database.ConnectionPool;
import com.epam.entity.AbstractEntity;
import com.epam.entity.Purchase;

public class PurchaseDAO implements IPurchaseDAO {
	
	private static final Logger LOG = Logger.getLogger(PurchaseDAO.class);
	private static final String INSERT_PURCHASE = "INSERT INTO purchases (user_id, voucher_id, date) values (?,?,?)";
	private static final String FIND_USER_PURCHASES = "SELECT * FROM purchases WHERE user_id = ?";
	private static final String FIND_ALL_PURCHASES = "SELECT * FROM purchases";
	private static final String FIND_ALL_PAGINATED = "SELECT * FROM purchases OFFSET ? LIMIT ?";
	private static final String FIND_ALL_PAGINATED_BY_LOGIN = "SELECT * FROM purchases WHERE user_id = ? OFFSET ? LIMIT ?";
	
	@Override
	public void insert (AbstractEntity entity) throws TourNotAvailableException {
	
		try {
			Connection connection = ConnectionPool.INSTANCE.takeConnection();
			PreparedStatement st = connection
					.prepareStatement(INSERT_PURCHASE);
			Purchase purchase = (Purchase) entity;
			
			st.setInt(1, purchase.getUserId());
			st.setInt(2, purchase.getVoucherId());
			st.setString(3, purchase.getDate().toString());
			st.execute();
			LOG.info("Purchase made " + purchase.toString());
			ConnectionPool.INSTANCE.retreiveConnection(connection);
			
		} catch (SQLException e) {
			LOG.error(e);
			throw new TourNotAvailableException();
		}
	}

	@Override
	public List<AbstractEntity> findAll(String login) {
		UserDAO userdao = new UserDAO();
		int id = userdao.findByLogin(login).getId();
		List<AbstractEntity> purchases = new ArrayList<AbstractEntity>();
		Purchase purchase;

		try {
			Connection connection = ConnectionPool.INSTANCE.takeConnection();
			PreparedStatement st = connection
					.prepareStatement(FIND_USER_PURCHASES);
			st.setInt(1, id);
			ResultSet rs = st.executeQuery();

			while (rs.next()) {
				purchase = construct(rs);
				purchases.add(purchase);
			}
			ConnectionPool.INSTANCE.retreiveConnection(connection);
			return purchases;
		} catch (SQLException e) {
			LOG.error(e);
		}
		return null;
	}
	
	@Override
	public List<AbstractEntity> findAll() {
		
		List<AbstractEntity> purchases = new ArrayList<AbstractEntity>();
		Purchase purchase;

		try {
			Connection connection = ConnectionPool.INSTANCE.takeConnection();
			PreparedStatement st = connection
					.prepareStatement(FIND_ALL_PURCHASES);

			ResultSet rs = st.executeQuery();
			

			while (rs.next()) {
				purchase = construct(rs);
				
				purchases.add(purchase);
			}
			ConnectionPool.INSTANCE.retreiveConnection(connection);
			return purchases;
		} catch (SQLException e) {
			LOG.error(e);
		}
		return null;

	}
	
	@Override
	public List<AbstractEntity> findAllPaginated(int offset,
			int numberOfRecords) {
		Connection connection = ConnectionPool.INSTANCE.takeConnection();
		List<AbstractEntity> purchases = new ArrayList<AbstractEntity>();

		try {
			PreparedStatement st = connection.prepareStatement(FIND_ALL_PAGINATED,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			st.setInt(1, offset);
			st.setInt(2, numberOfRecords);
			ResultSet rs = st.executeQuery();

			while (rs.next()) {
				Purchase p = construct(rs);
				purchases.add(p);
			}

		} catch (SQLException e) {
			LOG.error(e);
		}
		ConnectionPool.INSTANCE.retreiveConnection(connection);
		
		return purchases;

	}
	
	@Override
	public List<AbstractEntity> findAllPaginated(String login, int offset,
			int numberOfRecords) {
		
		Connection connection = ConnectionPool.INSTANCE.takeConnection();
		List<AbstractEntity> purchases = new ArrayList<AbstractEntity>();

		UserDAO pguserdao = new UserDAO();
		int id = pguserdao.findByLogin(login).getId();
		
		try {
			PreparedStatement st = connection.prepareStatement(FIND_ALL_PAGINATED_BY_LOGIN,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			st.setInt(1, id);
			st.setInt(2, offset);
			st.setInt(3, numberOfRecords);
			ResultSet rs = st.executeQuery();

			while (rs.next()) {
				
				Purchase p = construct(rs);
				purchases.add(p);
			}

		} catch (SQLException e) {
			LOG.error(e);
		}
		ConnectionPool.INSTANCE.retreiveConnection(connection);
		return purchases;

	}
	
	private Purchase construct(ResultSet rs) {
		
		int purchaseId = 0;
		int userId = 0;
		int voucherId = 0;
		String date = null;
		
		try {
		purchaseId = Integer.parseInt(rs.getString("purchase_id"));
		userId = Integer.parseInt(rs.getString("user_id"));
		voucherId = Integer.parseInt(rs.getString("voucher_id"));
		date = rs.getString("date"); 
		} catch (SQLException e) {
			LOG.error(e);
		}
		
		Purchase purchase = new Purchase.PurchaseBuilder().purchaseId(purchaseId).userId(userId).voucherId(voucherId).date(date).build();
		
		return purchase;
	}

	@Override
	public AbstractEntity findByID(int id) {		
		return null;
	}
	
}
